function REhtml(r)
{
	return r.replace(/</g,"&lt;").replace(/>/g,"&gt;").replace(/&/g,"&amp;");
}

function work_columns(space,submode)
{
	var info=space.site.sql.execute_and_fetch_one("pg_tableinfo/get",{table:space.fields.TABLE});
	space.columns=space.site.sql.execute_and_fetch("pg_tableinfo/columns",{id:info.oid});
	space.columns_skipped=[];
	space.columns.forEach(function(c) {
		switch (c.datatype) {
			case "integer":
			case "numeric":
			case "date":
			case "smallint":
			case "timestamp without time zone":
			case "double precision":
			case "character varying":
				c.ok=1;
				c.csv=1;
				break;
			case "text":
				if (submode=="edit" || submode=="import" || submode=="export") c.ok=1;
				c.csv=1;
				break;
		}
		if (!c.ok) space.columns_skipped.push({name:c.name,datatype:c.datatype});
	});
}

exports.controllers={
	admin_database: function(space) {
		if (!space.roles.database) return "Access denied";
		if (space.fields.TABLE) {
			if (!space.fields.TABLE.match(/^\w+$/)) return "Wrong table";
		}
		return space.controllers["admin_database__"+(space.fields.mode||"tables")](space);
	},
	admin_database__tables: function(space) {
		space.tables=space.site.sql.execute_and_fetch("pg_tableinfo/list");
		space.tables.forEach(function(t) { t.id=t.name; });
		return space.views.process("widgets/admin_database/tables",space);
	},

	admin_database__list: function(space) {
		space.cnt=space.site.sql.execute_and_fetch_one_single("select count(*) as cnt from "+space.fields.TABLE).cnt;
		var limit=100;
		work_columns(space,"list");
		var r="";
		var cols=[];
		space.columns.forEach(function(c) {
			if (!c.ok) return;
			cols.push(c.name);
		});
		if (cols) {
			space.site.sql.execute_and_fetch_single("select "+cols.join(",")+" from "+space.fields.TABLE+" limit :limit offset :offset",{limit:limit,offset: limit*(space.fields.page||0)}).forEach(function(line) {
				r+="<tr>";
				cols.forEach(function(cn) {
					if (line[cn]==null || line[cn]==undefined) {r+="<td class='sql-null'>NULL</td>";return;}
					r+="<td>";
					if (typeof line[cn]=="number") {
						r+=line[cn];
					} else {
						if (line[cn].length<50) {
							r+=REhtml(line[cn]);
						} else {
							r+=REhtml(line[cn].substr(0,50))+"...";
						}
					}
					r+="</td>";
				});
				r+="<td>";
				if (line.id) {
					r+="<a href='"+space.action+"?mode=edit&id="+line.id+"&TABLE="+space.fields.TABLE+"'>[E]</a> ";
					r+="<a href='"+space.action+"?mode=delete&id="+line.id+"&TABLE="+space.fields.TABLE+"'>[D]</a>";
				}
				r+="</td>";
				r+="</tr>";
			});
		}
		space.r=r;
		if (limit<space.cnt) {
			space.pages=[];
			for (var i=0;limit*i<space.cnt;i++) space.pages.push(i);
		}
		return space.views.process("widgets/admin_database/list",space);

	},

	admin_database__import: function(space) {
		work_columns(space,"import");
		var text=space.req.files.csv.data.toString("utf-8");
        var ssfunc=require(space.system.paths.controllers+'../server/spreadsheet.js').Spreadsheet;
        var ss=new ssfunc();
        var ws=ss.worksheet_add();
        var leftover=ws.csv_parse(text);
		var cols=[];
		var has_id=space.fields["COLUMN-id"]?1:0;
		var ins1="",ins2="",upd="";
		space.columns.forEach(function(c) {
			if (!c.ok) return;
			if (!space.fields["COLUMN-"+c.name]) return;
			cols.push(c.name);
			if (ins1) {
				ins1+=",";
				ins2+=",";
				upd+=",";
			}
			ins1+=c.name;
			ins2+=":"+c.name;
			upd+=c.name+"=:"+c.name;
		});

		var sqls={
			insert: "insert into "+space.fields.TABLE+" ("+ins1+") values ("+ins2+")",
			update: "update "+space.fields.TABLE+"\nset "+upd+" where id=:id",
			exists: "select 1 from "+space.fields.TABLE+" where id=:id"
		};
		for (var row=0;row<ws.rows;row++) {
			var h={};
			for (var i=0;i<cols.length;i++) h[cols[i]]=ws.read(row,i);
			if (has_id) {
				var e=space.site.sql.execute_and_fetch_one_single(sqls.exists,h);
				if (e) {
					space.site.sql.execute_single(sqls.update,h);
				} else {
					space.site.sql.execute_single(sqls.insert,h);
				}
			} else {
				space.site.sql.execute_single(sqls.insert,h);
			}
		}
		return space.views.process("widgets/admin_database/import_results",space);
	},

	admin_database__export: function(space) {
		space.cnt=space.site.sql.execute_and_fetch_one_single("select count(*) as cnt from "+space.fields.TABLE).cnt;
		work_columns(space,"export");
		var cols=[];
		space.columns.forEach(function(c) {
			if (!c.ok) return;
			if (!space.fields["COLUMN-"+c.name]) return;
			cols.push(c.name);
		});
		var ssfunc=require(space.system.paths.controllers+'../server/spreadsheet.js').Spreadsheet;
		var ss=new ssfunc();
		var ws=ss.worksheet_add();
		ws.grow(space.cnt-1,cols.length-1);
		var j=0;
		space.site.sql.execute_and_fetch_single("select "+cols.join(",")+" from "+space.fields.TABLE).forEach(function(line) {
			for (var i=0;i<cols.length;i++) {
				var v=line[cols[i]];
				if (v==undefined || v==null) v="";
				ws.write(j,i,v);
			}
			j++;
		});
		space.page.responseHeaders["Content-type"]='text/csv; charset=utf-8';
		space.fields.ajaj=1;
		return ws.csv_gen();
//		return "<pre>"+ws.csv_gen()+"</pre>";
	}
};

